﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Net;
using Avarton_Scanner.Logging;
using System.Data;
using System.Linq;
using System.Xml.Linq;

namespace Avarton_Scanner.Connection
{
    public class DatabaseClass
    {

        //class variables
        //database needs:
        SqlConnection thisConnection = new SqlConnection();
        SqlCommand thisCommand = new SqlCommand();
        SqlTransaction thisTransaction = null;
        SqlCommandBuilder thisCommandBuilder = null;
        SqlDataAdapter thisDataAdapter = new SqlDataAdapter();
        DataSet data = new DataSet();

        //Logging needs:
        Logger logger = new Logger();

        //class needs:
        String applicationName = "DatabaseClass";

        public String GetConnectionStringFromXml(String xmlUrl, String databaseName)
        {
            String connectionString = "";
            XDocument xdoc = XDocument.Load(xmlUrl);
            var obj = from db in xdoc.Descendants("Database")
                      where db.Attribute("name").Value == databaseName
                      select new
                      {
                          URL = (String) db.Element("ConnectionUrl").Value
                      };
            foreach (var x in obj)
            {
                connectionString = x.URL;
            }
            return connectionString;
        }

        /// <summary> 
        /// Method to make a connection with the default database.
        /// </summary>
        /// <returns>Returns a bool to indicate a succes or fail.</returns>
        /// <exception cref="SqlException"></exception>
        /// <seealso cref="CloseConnection"/>
        /// <seealso cref="LoggingService.Logger"/>
        public bool MakeConnection()
        {
            try
            {
                //read out the XML file
                thisConnection.ConnectionString = GetConnectionStringFromXml(@"..\..\Connection\DatabaseInformation.xml", "AvartonScanner");
                thisConnection.Open();
                return true;
            }
            catch (Exception ex)
            {
                logger.WriteLine(applicationName, ex.Message);
                return false;
            }
        }

        /// <summary>
        /// Method to close a connection with the default database.
        /// </summary>
        /// <returns>Returns a bool to indicate a succes or fail.</returns>
        /// <exception cref="SqlException"></exception>
        /// <seealso cref="MakeConnection"/>
        /// <seealso cref="LoggingService.Logger"/>
        public bool CloseConnection()
        {
            try
            {
                thisConnection.Close();
                return true;
            }
            catch (SqlException ex)
            {
                logger.WriteLine(applicationName, ex.Message);
                return false;
            }
        }

        /// <summary>
        /// **** 
        /// </summary>
        /// <returns>Returns a list of Network objects.</returns>
        /// <exception cref="Exception">Automatically closes the connection if any.</exception>
        /// <seealso cref="MakeConnection"/>
        /// <seealso cref="CloseConnection"/>
        /// <seealso cref="Logger"/>
        public List<Network> GetNetworks()
        {
            List<Network> networks = new List<Network>();            
            try
            {
                //make connection with database
                MakeConnection();
                //execute the query
                thisCommand = new SqlCommand("SELECT * FROM tblNetwork", thisConnection);

                //read text
                SqlDataReader thisReader = thisCommand.ExecuteReader();

                //read adminName from reader
                while (thisReader.Read())
                {
                    Network x = new Network();
                    //class Network:
                    //public int ID { get; set; }
                    //public String SSID { get; set; }
                    //public String MAC { get; set; }
                    //public IPAddress IP { get; set; }
                    //public DateTime FirstSeen { get; set; }
                    //public DateTime LastSeen { get; set; }

                    x.ID = (int)thisReader["ID"];
                    x.SSID = (String)thisReader["SSID"];
                    x.MAC = (String)thisReader["MAC"];
                    x.IP = (String)thisReader["IP"];
                    x.FirstSeen = (DateTime)thisReader["FirstSeen"];
                    x.LastSeen = (DateTime)thisReader["LastSeen"];
                    networks.Add(x);
                }

                //close reader and database connection
                thisReader.Close();
            }
            catch (Exception ex)
            {
                logger.WriteLine(applicationName, ex.Message);
            }
            finally
            {
                CloseConnection();
            }
            return networks;
        }

        /// <summary>
        /// **** 
        /// </summary>
        /// <returns>Returns a list of Client objects.</returns>
        /// <exception cref="Exception">Automatically closes the connection if any.</exception>
        /// <seealso cref="MakeConnection"/>
        /// <seealso cref="CloseConnection"/>
        /// <seealso cref="Logger"/>
        public List<Client> GetClients()
        {
            List<Client> clients = new List<Client>();            
            try
            {
                //make connection with database
                MakeConnection();
                //execute the query
                String query = "SELECT * FROM tblClient";
                thisDataAdapter.SelectCommand = new SqlCommand(query, thisConnection);
                DataSet ds = new DataSet();
                thisDataAdapter.Fill(ds, "tblClient");
                clients = (List<Client>)CollectionHelper.ConvertTo<Client>(ds.Tables[0]);
            }
            catch (Exception ex)
            {
                logger.WriteLine(applicationName, ex.Message);
            }
            finally
            {
                CloseConnection();
            }
            return clients;
        }

        public List<String> GetNetworkNames()
        {
            List<String> networkNames = new List<String>();
            try
            {
                    //make connection with database
                    MakeConnection();
                    //execute the query
                    thisCommand = new SqlCommand("SELECT DISTINCT SSID FROM tblClient", thisConnection);

                    //read text
                    SqlDataReader thisReader = thisCommand.ExecuteReader();

                    //read adminName from reader
                    while (thisReader.Read())
                    {
                        networkNames.Add(thisReader["SSID"].ToString());
                    }

                    //close reader and database connection
                    thisReader.Close();
            }
            catch (Exception ex)
            {
                logger.WriteLine(applicationName, ex.Message);
            }
            finally
            {
                CloseConnection();
            }
            return networkNames;
        }

        public List<Client> GetClientsWhere(String whereClause)
        {
            List<Client> data = new List<Client>();
            try
            {
                //make connection with database
                MakeConnection();
                //execute the query
                String query = "SELECT * FROM tblClient " + whereClause;
                thisDataAdapter.SelectCommand = new SqlCommand(query, thisConnection);
                DataSet ds = new DataSet();
                thisDataAdapter.Fill(ds, "tblClient");
                data = (List<Client>)CollectionHelper.ConvertTo<Client>(ds.Tables[0]);
            }
            catch (Exception ex)
            {
                logger.WriteLine(applicationName, ex.Message);
            }
            finally
            {
                CloseConnection();
            }
            return data;
        }

        public bool UpdateFromDataTable(DataTable dataTable)
        {
            /*
             * A third important point involves the call to the m_daTestScores object's Update method. 
             * When the program used the data adapter's Fill method to load data from the database, it passed 
             * the method the table's name as a parameter. Because the program did not create a TableMapping 
             * for the table, the adapter doesn't remember where it found the data in the database. To tell 
             * the program where it should apply its changes, the call to the Update method must include the 
             * name of the table as a parameter.
             */

            Boolean x = false;
            try
            {
                MakeConnection();
                SqlCommandBuilder commandBuilder = new SqlCommandBuilder(thisDataAdapter);
                switch (dataTable.TableName)
                { 
                    case "tblClient":
                        GetClients();
                        break;
                    case "tblNetwork":
                        GetNetworks();
                        break;
                }
                commandBuilder.RefreshSchema();
                thisDataAdapter.InsertCommand = commandBuilder.GetInsertCommand();
                thisDataAdapter.UpdateCommand = commandBuilder.GetUpdateCommand();
                thisDataAdapter.DeleteCommand = commandBuilder.GetDeleteCommand();
                thisDataAdapter.Update(dataTable);
                x = true;
            }
            catch (Exception ex)
            {
                logger.WriteLine(applicationName, ex.Message);
            }
            finally
            {
                CloseConnection();
            }
            return x;
        }

    }//end class
}
